package dao.impl;

import com.mysql.cj.util.StringUtils;
import dao.BaseDao;
import dao.Violation_recordDao;
import entity.Violation_record;

import javax.naming.NamingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Violation_recordDaoimpl extends BaseDao implements Violation_recordDao {

    @Override
    public List<Violation_record> Violation_record(Integer searchKey, Integer currentPage, Integer pageSize)
            throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Violation_record> list = new ArrayList<>();

        try {

            StringBuilder sql = new StringBuilder(
                    "SELECT id, student_id, student_name, violation_type, punishment, " +
                            "record_time, auditor, status FROM violation WHERE 1=1");

            if (searchKey != null) {
                sql.append(" AND student_id = ?");
            }


            int offsetPage=currentPage*pageSize;

            sql.append(" LIMIT ?, ?");

            pstm = conn.prepareStatement(sql.toString());
            int paramIndex = 1;
            if (searchKey != null) {
                pstm.setInt(paramIndex++, searchKey);
            }
            pstm.setInt(paramIndex++, offsetPage);
            pstm.setInt(paramIndex, pageSize);

            rs = pstm.executeQuery();
            while (rs.next()) {
                Violation_record mod = new Violation_record();
                mod.setId(String.valueOf(rs.getInt("id")));
                mod.setStudent_id(rs.getString("student_id"));
                mod.setStudent_name(rs.getString("student_name"));
                mod.setViolation_type(rs.getString("violation_type"));
                mod.setPunishment(rs.getString("punishment"));
                mod.setRecord_time(rs.getString("record_time"));
                mod.setAuditor(rs.getString("auditor"));
                mod.setStatus(rs.getString("status"));
                list.add(mod);
            }
            return list;
        } finally {
            closeResources(rs, pstm, conn);
        }
    }

    @Override
    public int INSERT(Integer student_id, String student_name, String violation_type,
                      String punishment, String record_time, String auditor, String status)
            throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;

        try {

            String sql = "INSERT INTO violation (student_id, student_name, violation_type, " +
                    "punishment, record_time, auditor, status) VALUES (?, ?, ?, ?, ?, ?, ?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, student_id);
            ps.setString(2, student_name);
            ps.setString(3, violation_type);
            ps.setString(4, punishment);
            ps.setString(5, record_time);
            ps.setString(6, auditor);
            ps.setString(7, status);
            return ps.executeUpdate();
        } finally {
            closeResources(null, ps, conn);
        }
    }

    @Override
    public int UPDATE(String id, String violation_type, String punishment,
                      String record_time, String auditor, String status)
            throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;

        try {

            String sql = "UPDATE violation SET violation_type=?, punishment=?, " +
                    "record_time=?, auditor=?, status=? WHERE id=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, violation_type);
            ps.setString(2, punishment);
            ps.setString(3, record_time);
            ps.setString(4, auditor);
            ps.setString(5, status);
            ps.setString(6, id);
            return ps.executeUpdate();
        } finally {
            closeResources(null, ps, conn);
        }
    }

    @Override
    public int select(String name) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;

        try {

            String sql = "SELECT student_id FROM students WHERE name=?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, name);
            rs = pstm.executeQuery();
            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            closeResources(rs, pstm, conn);
        }
    }

    @Override
    public int delete(int id) throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement pt = null;

        try {

            String sql = "DELETE FROM violation WHERE id=?";
            pt = conn.prepareStatement(sql);
            pt.setInt(1, id);
            return pt.executeUpdate();
        } finally {
            closeResources(null, pt, conn);
        }
    }

    @Override
    public int getTotalCount(String searchKey) throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM violation WHERE 1=1");

            if (!StringUtils.isNullOrEmpty(searchKey)) {
                sql.append(" AND (student_id LIKE ? OR student_name LIKE ? OR violation_type LIKE ?)");
            }

            ps = conn.prepareStatement(sql.toString());

            if (!StringUtils.isNullOrEmpty(searchKey)) {
                String searchPattern = "%" + searchKey + "%";
                ps.setString(1, searchPattern);
                ps.setString(2, searchPattern);
                ps.setString(3, searchPattern);
            }

            rs = ps.executeQuery();
            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            closeResources(rs, ps, conn);
        }
    }

    // 辅助方法：关闭数据库资源
    private void closeResources(ResultSet rs, PreparedStatement stmt, Connection conn) {
        try {
            if (rs != null) rs.close();
        } catch (SQLException e) {
            System.err.println("关闭ResultSet失败: " + e.getMessage());
        }

        try {
            if (stmt != null) stmt.close();
        } catch (SQLException e) {
            System.err.println("关闭PreparedStatement失败: " + e.getMessage());
        }

        try {
            if (conn != null) conn.close();
        } catch (SQLException e) {
            System.err.println("关闭Connection失败: " + e.getMessage());
        }
    }
}